﻿using DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CToolsWin.DAL
{
    public class SQLInfos
    {
        protected DBHelpers db = DBFactorys.GetDBHelper();
        /// <summary>
        /// 获取数据库信息
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public DataTable GetInfo(string connStr)
        {
            string sql = @"SELECT a.name fileName
    ,cast(a.[size]*1.0/128 AS DECIMAL(12,1)) AS fileSize
    ,CAST(FILEPROPERTY(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS fileUsedSize
    ,CAST((FILEPROPERTY(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0  AS DECIMAL(12,1)) AS fileUsedSizeRatio
    ,CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' END AS increaseType
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小'
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示'
        ELSE '文件大小固定，不会增长' END AS incrementType
    ,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN CAST(CAST(a.growth*1.0/128 AS DECIMAL(12,0)) AS VARCHAR)+'MB'
        WHEN A.growth > 0 AND is_percent_growth = 1 THEN CAST(CAST(a.growth AS DECIMAL(12,0)) AS VARCHAR)+'%'
        ELSE '文件大小固定，不会增长' end AS increaseValue
    ,a.physical_name AS fileDirectory
    ,a.type_desc AS fileType
FROM sys.database_files  a
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]
ORDER BY a.[type]";
            return db.GetTable(connStr, CommandType.Text, sql, null)[0];
        }
        public DataTable GetTables(string connStr)
        {
            string sql = @"SELECT name,id FROM SysObjects WHERE XType='U' AND Status>=0 ORDER BY name";
            return db.GetTable(connStr, CommandType.Text, sql, null)[0];
        }
        /// <summary>
        /// 获取数据库所有表名列名
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public DataTable GetColumns(string connStr, string tableName = "")
        {
            StringBuilder sql = new StringBuilder(@"SELECT T.Name AS TableName,S.Name AS ColumnName
FROM SysColumns S
LEFT JOIN SysObjects T ON S.id=Object_Id(T.Name)
WHERE T.XType='U'");
            if (string.IsNullOrWhiteSpace(tableName) == false)
                sql.Append(" AND T.Name=" + tableName);

            sql.Append(" ORDER BY T.Name,S.Name");
            return db.GetTable(connStr, CommandType.Text, sql.ToString(), null)[0];
        }
        /// <summary>
        /// 获取数据库表信息
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetTableInfo(string connStr, int tableId, string tableName = "")
        {
            string where = "";
            if (tableId > 0)
                where = " AND a.id=" + tableId;
            if (string.IsNullOrWhiteSpace(tableName) == false)
                where += " AND a.id=(SELECT TOP 1 d.id FROM sysobjects d WHERE d.xtype= 'U' AND d.name <> 'dtproperties' AND d.name='" + tableName + "')";
            string sql = string.Format(@"SELECT a.[name] AS ColumnName,
	a.id,
    CASE WHEN COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 THEN '是' ELSE '' END AS IsIdentity,
    CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'PK' AND name IN (
	SELECT name FROM sysindexes WHERE indid IN(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) THEN '是' ELSE '' END AS PrimaryKey,
    b.[name] AS DataType,
    a.[length] AS TakeBytes,
    COLUMNPROPERTY(a.[id],a.[name], 'PRECISION') AS DataPrecision,
    ISNULL(COLUMNPROPERTY(a.[id],a.[name], 'Scale'),0) AS DecimalScale,
    CASE WHEN a.[isnullable]=1 THEN '是' ELSE '否' END AS IsAllowNull,
    ISNULL(e.[text], '') AS DefaultValue,
    ISNULL(g.[value], '') AS ColumnExplain
FROM syscolumns a
    LEFT JOIN systypes b ON a.xtype=b.xusertype
    LEFT JOIN syscomments e ON a.cdefault=e.id
    LEFT JOIN sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id AND g.name='MS_Description'
WHERE 1=1{0}
ORDER BY a.id,a.colorder", where);
            return db.GetTable(connStr, CommandType.Text, sql, null)[0];
        }
    }
}
